﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;

namespace DAL
{
    /// <summary>
    /// DAL to Guest table
    /// </summary>
    public abstract class GuestDAL : BaseDAL, UTL.IBaseDAL
    {
        #region Contansts
        #endregion

        #region Objects
        #endregion

        #region Properties
        #endregion

        #region Constructors
        public GuestDAL() { }
        public GuestDAL(string connectString) : base(connectString) { }
        #endregion

        #region Implements
        /// <summary>
        /// Count all of records
        /// </summary>
        /// <returns>Number of records</returns>
        public int Count()
        {
            return _mdb.tb_Guests.Count();
        }

        /// <summary>
        /// Select all data from table in database
        /// </summary>
        /// <returns>Data</returns>
        public DataTable Select()
        {
            try
            {
                //return ExecuteQuery(@"select * from tb_Guest");

                var res = from s in _mdb.tb_Guests
                          orderby s.Name
                          select new
                          {
                              s.Id,
                              s.Name,
                              s.IdCard,
                              s.Address,
                              s.Birthday,
                              s.Sex,
                              s.Telephone,
                              s.Note,
                              s.Photo
                          };
                return res.ToDataTable();
            }
            catch { return _dtb; }
        }

        /// <summary>
        /// Select data by object from table in database
        /// </summary>
        /// <param name="obj">Condition</param>
        /// <returns>Data</returns>
        public DataTable Select(object obj)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// Get a object by key
        /// </summary>
        /// <param name="key">Key need to get</param>
        /// <returns>Object</returns>
        public object GetByKey(object key)
        {
            try
            {
                var res = _mdb.tb_Guests.Single(k => k.IdCard == key + "");
                
                return res;
            }
            catch { return null; }
        }

        /// <summary>
        /// Delete data by id
        /// </summary>
        /// <param name="id">Id</param>
        /// <returns>True is successfull else false</returns>
        public UTL.Session.PerState Delete(long id)
        {
            /*var sql = @"delete from tb_Guest where id = {0}";
            sql = String.Format(sql, id);
            return ExecuteNonQuery(sql) > 0 ? true : false;*/

            try
            {
                _mdb = new xG14DataContext();

                var res = from s in _mdb.tb_Guests
                          where s.Id == id
                          select s;
                if (res != null)
                {
                    var et = res.Single();
                    _mdb.tb_Guests.DeleteOnSubmit(et);
                    _mdb.SubmitChanges();

                    return UTL.Session.PerState.Success;
                }
                else { return UTL.Session.PerState.Undelete; }
            }
            catch { return UTL.Session.PerState.Undelete; }
        }

        /// <summary>
        /// Insert object to table in database
        /// </summary>
        /// <param name="obj">Object need to insert</param>
        /// <returns>True is successfull else false</returns>
        public UTL.Session.PerState Insert(object obj)
        {
            /*var o = (tb_Guest)obj;
            var sql = @"insert into tb_Guest(Name, [Address], Birthday, Sex, RoomId, Telephone, MobilePhone, Photo, Note) values(N'{0}', N'{1}', '{2}', {3}, {4}, '{5}', '{6}', '{7}', N'{8}')";
            sql = String.Format(sql, o.Name, o.Address, o.Birthday, o.Sex ? 1 : 0, o.RoomId, o.Telephone, o.MobilePhone, o.Photo, o.Note);
            return ExecuteNonQuery(sql) > 0 ? true : false;*/

            var o = (tb_Guest)obj;

            try
            {
                _mdb.tb_Guests.Single(k => k.IdCard == o.IdCard);
                return UTL.Session.PerState.Duplicate;
            }
            catch
            {
                try
                {

                    _mdb.tb_Guests.InsertOnSubmit(o);

                    _mdb.SubmitChanges();
                    return UTL.Session.PerState.Success;
                }
                catch { return UTL.Session.PerState.Uninsert; }
            }
        }

        /// <summary>
        /// Update object to table in database
        /// </summary>
        /// <param name="obj">Object need to update</param>
        /// <returns>True is successfull else false</returns>
        public UTL.Session.PerState Update(object obj)
        {
            /*var o = (tb_Guest)obj;
            var sql = @"update tb_Guest set Name = N'{0}', [Address] = N'{1}', Birthday = '{2}', Sex = {3}, RoomId = {4}, Telephone = '{5}', MobilePhone = '{6}', Photo = '{7}', Note = N'{8}' where Id = {9}";
            sql = String.Format(sql, o.Name, o.Address, o.Birthday, o.Sex ? 1 : 0, o.RoomId, o.Telephone, o.MobilePhone, o.Photo, o.Note, o.Id);
            return ExecuteNonQuery(sql) > 0 ? true : false;*/

            var o = (tb_Guest)obj;

            try
            {
                var res = _mdb.tb_Guests.Single(k => k.Id == o.Id);

                res.Name = o.Name;
                res.IdCard = o.IdCard;
                res.Birthday = o.Birthday;
                res.Sex = o.Sex;
                res.Address = o.Address;
                res.Telephone = o.Telephone;
                res.Note = o.Note;
                res.Photo = o.Photo;

                _mdb.SubmitChanges();
                return UTL.Session.PerState.Success;
            }
            catch { return UTL.Session.PerState.Unupdate; }
        }
        #endregion

        #region Events
        #endregion

        #region Methods
        /// <summary>
        /// Get list guest no lease room
        /// </summary>
        /// <returns></returns>
        public DataTable GetNoLease()
        {
            try
            {
                var res = from s in _mdb.tb_Guests
                          where !_mdb.tb_Leases.Any(p => p.GuestId == s.Id)
                          orderby s.Name
                          select new
                          {
                              s.Id,
                              s.Name,
                              s.Address,
                              s.Birthday,
                              s.Sex,
                              s.Telephone,
                              s.Note,
                              s.Photo
                          };
                return res.ToDataTable();
            }
            catch { return _dtb; }
        }
        #endregion

        #region Overrides
        #endregion

        #region Statics
        #endregion

        #region More
        #endregion
    }
}